Online analytical processing (OLAP) is a key part of many data warehouse and business analysis systems. OLAP services provide for fast analysis of multidimensional information. For this purpose, OLAP services provide for multidimensional access and navigation of data in an intuitive and natural way, providing a global view of data that can be drilled down into particular data of interest. Speed and response time are important attributes of OLAP services that allow users to browse and analyze data online in an efficient manner. Further, OLAP services typically provide analytical tools to rank, aggregate, and calculate lead and lag indicators for the data under analysis.
In this context, an OLAP cube may be modeled according to a user's perception of the data. A cube may organize a data type according to dimensions, each dimension modeled according to an attribute of the data type. For example, a cube may organize “Sales” data according to the dimensions “Customer”, “Location” and “Time.” Typically, there is a hierarchy associated with each dimension. The hierarchy includes levels of granularity. For example, the time dimension can consist of years subdivided into quarters subdivided into months subdivided into weeks subdivided into days. The years level is the broadest level of granularity, while the days level is the finest level of granularity. Dimension members act as indices for identifying a particular cell or range of cells within the cube. The cube may also have measures, which measure a data type according to its attributes. For example, the “Sales” cube may have a measure “Sales Amount”, which is the amount of sales made in source currencies of a corresponding location.
OLAP services are often used to analytically model data that is stored in a relational database such as, for example, an Online Transactional Processing (OLTP) database. Data stored in such a relational database may be organized according to multiple tables. Each such table may organize a data type according to columns corresponding to attributes and measures. For example, the “Sales” cube may be modeled according to a “Sales” table with columns corresponding to attributes “Customer”, “Location”, and “Time” and measure “Sales Amount”. Furthermore, an “Exchange” table may include columns corresponding to attributes “Location” and “Time” and measure “Exchange Rate”, with “Exchange Rate” being the rate of conversion of a source currency of a corresponding location into US currency.
It is often desirable to calculate relationships between different measures. For example, it may be desirable to calculate the product of the “Sales Amount” measure from the “Sales” table and the “Exchange Rate” measure of the “Exchange” table to convert sales from a source currency into US currency. Such a relationship between measures is first calculated at the finest level of granularity for which data is available, and then the calculated results are subsequently aggregated to produce results for a broader selected range. For example, if the finest level of granularity for both “Sales” data and the “Exchange” data is the month level, then a request to convert Mexican and Canadian currency sales into US currency sales for a quarter is calculated for each of the two source currencies in each of the three months of the quarter, resulting in six separate calculations. The six calculations are subsequently aggregated to provide total Mexican and Canadian sales in US currency for the entire quarter.
In conventional OLAP systems, data for each measure must be separately accessed for each calculation from each table on a row by row basis. For example, to convert Mexican and Canadian sales for the quarter, the “Sales” table and the “Exchange” table must each be separately accessed six times for each of the six rows involved in the calculation. Such separate access on a row by row basis may significantly prolong the amount of time required to perform complex calculations. Thus, there is a need in the art for systems and methods for calculating a relationship between measures in which the relationship is not calculated on a row by row basis.